package com.hk.housekeeping.order.dao.impl;

import com.hk.housekeeping.order.dao.OrderDao;
import com.hk.housekeeping.order.entity.Order;
import com.hk.housekeeping.order.utils.OrderUtils;
import com.hk.housekeeping.order.utils.PrepareStatementUtils;
import com.hk.housekeeping.order.utils.DBManager;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class OrderDaoImpl implements OrderDao {

    @Override
    public List<Order> getPageList(int currentPage, int pageSize) throws SQLException {
        ArrayList<Order> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Order order = null;
        try {
            conn = DBManager.getConnection();
            String sql = "select * from sale_order limit ?,?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, currentPage);
            pStmt.setInt(2, pageSize);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                order = new Order();
                order.setSid(rs.getString("sid"));
                order.setClientName(rs.getString("client_name"));
                order.setClientPhone(rs.getString("client_phone"));
                order.setOrderState(rs.getInt("order_state"));
                order.setCreateTime(rs.getDate("create_time"));
                order.setIsDelete(rs.getInt("is_delete"));

                list.add(order);
            }
        } finally {
            DBManager.close(rs, pStmt, conn);
        }
        return list;
    }

    @Override
    public int getTotalCount() throws SQLException {
        Connection conn = null;
        ResultSet rs = null;
        Statement stmt = null;
        int totalCount = 0;
        try {
            String sql = "select count(*) from sale_order";
            conn = DBManager.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
//            while (rs.next()) {
//                totalCount = rs.getInt(1);
//            }
            totalCount = rs.next() ? rs.getInt(1) : 0;
        } finally {
            DBManager.close(rs, stmt, conn);
        }
        return totalCount;
    }

    @Override
    public Order getOrderContent(String sid) throws SQLException {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pStmt = null;
        conn = DBManager.getConnection();
        Order order = null;
        try {
            String sql = "select * from sale_order where sid = ?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setString(1, sid);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                order = new Order();
                order.setSid(rs.getString("sid"));
                order.setClientName(rs.getString("client_name"));
                order.setClientPhone(rs.getString("client_phone"));
                order.setOrderState(rs.getInt("order_state"));
                order.setCreateTime(rs.getDate("create_time"));
                order.setIsDelete(rs.getInt("is_delete"));
            }
        } finally {
            DBManager.close(rs, pStmt, conn);
        }

        return order;
    }

    @Override
    public List<Order> getStateList(int state, int page, int pageSize) throws SQLException {
        ArrayList<Order> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Order order = null;
        try {
            conn = DBManager.getConnection();
            String sql = "select * from sale_order where order_state = ? limit ?,?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, state);
            pStmt.setInt(2, page);
            pStmt.setInt(3, pageSize);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                order = new Order();
                order.setSid(rs.getString("sid"));
                order.setClientName(rs.getString("client_name"));
                order.setClientPhone(rs.getString("client_phone"));
                order.setOrderState(rs.getInt("order_state"));
                order.setCreateTime(rs.getDate("create_time"));
                order.setIsDelete(rs.getInt("is_delete"));

                list.add(order);
            }
        } finally {
            DBManager.close(rs, pStmt, conn);
        }
        return list;
    }

    @Override
    public int getStateCount(int state) throws SQLException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
            String sql = "select count(*) from sale_order where order_state = ?";
            conn = DBManager.getConnection();
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, state);
            rs = pStmt.executeQuery();

            return rs.next() ? rs.getInt(1) : 0;
        } finally {
            DBManager.close(rs, pStmt, conn);
        }
    }

    @Override
    public List<Order> getPhoneList(String phone, int state, int page, int pageSize) throws SQLException {
        ArrayList<Order> list = new ArrayList<>();
//        //获取指定状态的Order对象集合
//        List<Order> stateList = getStateList(state, page, pageSize);
//        //List为有序集合，将起始页当作开始寻找的索引
//        for (int i = page; i < page + pageSize; i++) {
//            if (stateList.get(i).getClientPhone().equals(phone)) {
//                list.add(list.get(i));
//            }
//        }

        //以上方法不可行，因为stateList已经是经过了分页限制得出的集合，从该集合里面【继续】选就会局限了范围，该范围之外还可能存在对应的记录没有显示
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Order order = null;
        try {
            conn = DBManager.getConnection();
            String sql = "select * from sale_order where order_state = ? and client_phone = ? limit ?,?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, state);
            pStmt.setString(2, phone);
            pStmt.setInt(3, page);
            pStmt.setInt(4, pageSize);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                order = new Order();
                order.setSid(rs.getString("sid"));
                order.setClientName(rs.getString("client_name"));
                order.setClientPhone(rs.getString("client_phone"));
                order.setOrderState(rs.getInt("order_state"));
                order.setCreateTime(rs.getDate("create_time"));
                order.setIsDelete(rs.getInt("is_delete"));

                list.add(order);
            }
        } finally {
            DBManager.close(rs, pStmt, conn);
        }

        return list;
    }

    @Override
    public int getStateAndPhoneCount(int state, String phone) throws SQLException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        int totalCount = 0;
        try {
            conn = DBManager.getConnection();
            String sql = "select count(*) from sale_order where order_state = ? and client_phone = ?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, state);
            pStmt.setString(2, phone);
            rs = pStmt.executeQuery();
            totalCount = rs.next() ? rs.getInt(1) : 0;
        } finally {
            DBManager.close(rs, pStmt, conn);
        }
        return totalCount;
    }

    @Override
    public List<Order> getDateList(Date startTime, Date endTime, int page, int pageSize) throws SQLException {
        ArrayList<Order> list = new ArrayList<>();

        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        Order order = null;
        try {
            conn = DBManager.getConnection();
            String sql = "select * from sale_order where create_time between ? and ? limit ?,?";
            pStmt = conn.prepareStatement(sql);
            PrepareStatementUtils.SetValueToSQL(pStmt, startTime, endTime, page, pageSize);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                order = OrderUtils.getOrderBySelect(rs);
                list.add(order);
            }
        }finally {
            DBManager.close(rs, pStmt, conn);
        }

        return list;
    }

    @Override
    public int getDateCount(Date startTime, Date endTime) throws SQLException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        int count = 0;
        try {
            conn = DBManager.getConnection();
            String sql = "select count(*) from sale_order where create_time between ? and ?";
            pStmt = conn.prepareStatement(sql);
            PrepareStatementUtils.SetValueToSQL(pStmt, startTime, endTime);
            rs = pStmt.executeQuery();
            count = rs.next() ? rs.getInt(1) : 0;
        } finally {
            DBManager.close(rs, pStmt, conn);
        }

        return count;
    }
}
